package com.example.util;

import cn.hutool.setting.dialect.Props;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JDBCUtil {
    private static String url;
    private static String username;
    private static String password;
    static Props props = new Props("db.properties");
    static {
        try {
            Class.forName(props.getProperty("driverClass"));
            url = props.getProperty("url");
            username = props.getProperty("username");
            password = props.getProperty("password");
        } catch (ClassNotFoundException e) {
            //ClassNotFoundException: com.mysql.jdbc.Driver找不到
            //原因: 没有导入mysql驱动
            e.printStackTrace();
        }
    }

    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(url, username,password);
    }

    public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection){
        try {
            if(resultSet!=null){
                resultSet.close();
            }
            if(preparedStatement!=null){
                preparedStatement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static  <T> T getInstance(Class<T> clazz,String sql,Object...args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConn();

            preparedStatement = connection.prepareStatement(sql);
            if (args!=null) {
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i + 1, args[i]);
                }
            }

            resultSet = preparedStatement.executeQuery();

            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

            int columnCount = resultSetMetaData.getColumnCount();

            if (resultSet.next()) {
                T t = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columValue = resultSet.getObject(i+1);

                    //获取每个列的列名
                    String columnLabel = resultSetMetaData.getColumnLabel(i+1);

                    //给t对象指定columName属性，赋值为columValue：通过反射

                    Field field = clazz.getDeclaredField(getDbName(columnLabel));
                    field.setAccessible(true);
                    field.set(t, columValue);

                }
                return t;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }

        return null;

    }

    //返回多个值
    public static  <T> List<T> getForList(Class<T> clazz, String sql, Object...args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConn();

            preparedStatement = connection.prepareStatement(sql);

            if (args!=null){
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i+1,args[i]);
                }
            }

            resultSet = preparedStatement.executeQuery();

            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int Count = resultSetMetaData.getColumnCount();
            //集合
            List<T> list = new ArrayList<>();
            while (resultSet.next()){
                T t =  clazz.newInstance();

                for (int i = 0; i < Count; i++) {
                    //获取列值
                    Object Value = resultSet.getObject(i+1);

                    //获取每个列的列名
                    String Label = resultSetMetaData.getColumnLabel(i+1);

                    //给t对象指定columName属性，赋值为columValue：通过反射
                    Field field = clazz.getDeclaredField(getDbName(Label));
                    field.setAccessible(true);
                    field.set(t,Value);

                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    //增删改
    public static int ALLCUD(String sql,Object...args){
        Connection connection =  null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConn();
            preparedStatement=connection.prepareStatement(sql);
            if (args!=null) {
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i + 1, args[i]);
                }
            }
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(null,preparedStatement,connection);
        }
        return -1;
    }

    //工具类：属性名转化。把数据库里有斜划线的列名转化成骆驼命名法的格式。
    private static String getDbName(String columnName){
        char[] chars = columnName.toCharArray();
        StringBuffer s = new StringBuffer();
        for (int i = 0 ; i < chars.length ; i++){
            if(chars[i] == '_'){
                s.append(String.valueOf(chars[i+1]).toUpperCase());
                i++;
            }else {
                s.append(String.valueOf(chars[i]));
            }
        }
        return  s.toString();
    }
}
